package com.opennews.openplatform

import com.opennews.openplatform.constants.IncomeExpenseTypeConstant
import com.opennews.openplatform.constants.PaymentTypeConstant
import grails.compiler.GrailsCompileStatic
import grails.gorm.transactions.Transactional
import groovy.transform.CompileDynamic

import java.text.DecimalFormat

@Transactional(readOnly = true)
@GrailsCompileStatic
class IncomeExpenseAnalysisService {
    BankService bankService
    IncomeExpenseDetailTypeService incomeExpenseDetailTypeService

    /**
     * Queries income expense summary based one month. Includes total income, total expense and balance.
     * @param accountGroupId: The string of the user's account group id.
     * @param uniqueDates: Stores each incomeExpenseDate without duplicate.
     * @return The list of map instance which contains keys below.
     *          incomeExpenseDate: The date of income or expense happens. No duplicate date in this list.
     *          cashIncome: Float of the total cash income in this month.
     *          cashExpense: Float of the total cash expense in this month.
     *          cashBalance: Float of the total cash balance in this month.
     *          totalCreditCardIncome: Float of the total credit card income in this month.
     *          totalCreditCardExpense: Float of the total credit card expense in this month.
     *          creditCardBalance: Float of the total credit card balance in this month.
     */
    @CompileDynamic
    List<Map<String, ?>> queryMonthlySummaryList(String accountGroupId, Set<Date> uniqueDates) {
        List<Map<String, ?>> list = []

        uniqueDates.each { Date date ->
            def hql = """
                        SELECT
                            COALESCE(SUM(amount), 0),
                            paymentType,
                            type
                        FROM
                            IncomeExpenseDetail
                        WHERE
                            accountGroup.id = :accountGroupId AND
                            YEAR(incomeExpenseDate) = YEAR(:date) AND
                            MONTH(incomeExpenseDate) = MONTH(:date)
                        GROUP BY
                            YEAR(incomeExpenseDate),
                            MONTH(incomeExpenseDate),
                            paymentType,
                            type
                      """

            List<Map<String, ?>> rawData = IncomeExpenseDetail.executeQuery(hql, [
                accountGroupId: accountGroupId,
                date: date
            ]).collect {[
                amount: it[0],
                paymentType: it[1],
                type: it[2]
            ]}

            BigDecimal cashIncome = rawData.findAll { Map<String, ?> data ->
                data["paymentType"] == PaymentTypeConstant.CASH &&
                    data["type"] == IncomeExpenseTypeConstant.INCOME
            }.sum { Map<String, ?> data -> data["amount"] } ?: 0.00

            BigDecimal cashExpense = rawData.findAll { Map<String, ?> data ->
                data["paymentType"] == PaymentTypeConstant.CASH &&
                    data["type"] == IncomeExpenseTypeConstant.EXPENSE
            }.sum { Map<String, ?> data -> data["amount"] } ?: 0.00

            BigDecimal creditCardIncome = rawData.findAll { Map<String, ?> data ->
                data["paymentType"] == PaymentTypeConstant.CREDIT_CARD &&
                    data["type"] == IncomeExpenseTypeConstant.INCOME
            }.sum { Map<String, ?> data -> data["amount"] } ?: 0.00

            BigDecimal creditCardExpense = rawData.findAll { Map<String, ?> data ->
                data["paymentType"] == PaymentTypeConstant.CREDIT_CARD &&
                    data["type"] == IncomeExpenseTypeConstant.EXPENSE
            }.sum { Map<String, ?> data -> data["amount"] } ?: 0.00

            list << [
                incomeExpenseDate: date,
                cashIncome: cashIncome,
                cashExpense: cashExpense,
                cashBalance: cashIncome + cashExpense,
                creditCardIncome: creditCardIncome,
                creditCardExpense: creditCardExpense,
                creditCardBalance: creditCardIncome + creditCardExpense
            ]
        }

        return list
    }

    /**
     * Queries income expense summary based one single day. Includes total income, total expense and balance.
     * @param accountGroupId: The string of the user's account group id.
     * @param dates: The date list which stores each incomeExpenseDate might duplicate.
     * @return The list of map instance which contains keys below.
     *          incomeExpenseDate: The date of income or expense happens. No duplicate date in this list.
     *          totalIncome: Float of the total income in this day.
     *          totalExpense: Float of the total expense in this day.
     *          balance: Float of the balance in this day.
     */
    List<Map<String, ?>> queryMonthlySummaryList(String accountGroupId, List<Date> dates) {
        // Gets unique date from the list.
        def uniqueDates = new HashSet<Date>(dates.collect {
            DateHelper.getFirstDayOfMonth(it)
        })

        return queryMonthlySummaryList(accountGroupId, uniqueDates)
    }

    /**
     * Queries income expense summary based one single day. Includes total income, total expense and balance.
     * @param accountGroupId: The string of the user's account group id.
     * @param uniqueDates: Stores each incomeExpenseDate without duplicate.
     * @return The list of map instance which contains keys below.
     *          incomeExpenseDate: The date of income or expense happens. No duplicate date in this list.
     *          cashIncome: Float of the total cash income in this day.
     *          cashExpense: Float of the total cash expense in this day.
     *          cashBalance: Float of the total cash balance in this day.
     *          creditCardIncome: Float of the total credit card income in this day.
     *          creditCardExpense: Float of the total credit card expense in this day.
     *          creditCardBalance: Float of the total credit card balance in this day.
     */
    @CompileDynamic
    List<Map<String, ?>> queryDailySummaryList(String accountGroupId, Set<Date> uniqueDates) {
        if (!uniqueDates?.empty) {
            def hql = """
                        SELECT
                            incomeExpenseDate,
                            COALESCE(SUM(amount), 0),
                            paymentType,
                            type
                        FROM
                            IncomeExpenseDetail
                        WHERE
                            accountGroup.id = :accountGroupId AND
                            incomeExpenseDate IN :dates
                        GROUP BY
                            incomeExpenseDate,
                            paymentType,
                            type
                      """

            List<Map<String, ?>> rawData = IncomeExpenseDetail.executeQuery(hql, [
                accountGroupId: accountGroupId,
                dates: uniqueDates
            ]).collect {[
                incomeExpenseDate: it[0],
                amount: it[1],
                paymentType: it[2],
                type: it[3]
            ]}

            return uniqueDates.collect { Date date ->
                List<Map<String, ?>> dateItems = rawData.findAll { Map<String, ?> data -> data["incomeExpenseDate"] == date }

                BigDecimal cashIncome = dateItems.findAll { Map<String, ?> data ->
                    data["paymentType"] == PaymentTypeConstant.CASH &&
                        data["type"] == IncomeExpenseTypeConstant.INCOME
                }.sum { Map<String, ?> data -> data["amount"] } ?: 0.00

                BigDecimal cashExpense = dateItems.findAll { Map<String, ?> data ->
                    data["paymentType"] == PaymentTypeConstant.CASH &&
                        data["type"] == IncomeExpenseTypeConstant.EXPENSE
                }.sum { Map<String, ?> data -> data["amount"] } ?: 0.00

                BigDecimal creditCardIncome = dateItems.findAll { Map<String, ?> data ->
                    data["paymentType"] == PaymentTypeConstant.CREDIT_CARD &&
                        data["type"] == IncomeExpenseTypeConstant.INCOME
                }.sum { Map<String, ?> data -> data["amount"] } ?: 0.00

                BigDecimal creditCardExpense = dateItems.findAll { Map<String, ?> data ->
                    data["paymentType"] == PaymentTypeConstant.CREDIT_CARD &&
                        data["type"] == IncomeExpenseTypeConstant.EXPENSE
                }.sum { Map<String, ?> data -> data["amount"] } ?: 0.00

                return [
                    incomeExpenseDate: date,
                    cashIncome: cashIncome,
                    cashExpense: cashExpense,
                    cashBalance: cashIncome + cashExpense,
                    creditCardIncome: creditCardIncome,
                    creditCardExpense: creditCardExpense,
                    creditCardBalance: creditCardIncome + creditCardExpense
                ]
            } as List<Map<String, ?>>
        } else {
            return []
        }
    }

    /**
     * Queries income expense summary based one single day. Includes total income, total expense and balance.
     * @param accountGroupId: The string of the user's account group id.
     * @param dates: The date list which stores each incomeExpenseDate might duplicate.
     * @return The list of map instance which contains keys below.
     *          incomeExpenseDate: The date of income or expense happens. No duplicate date in this list.
     *          totalIncome: Float of the total income in this day.
     *          totalExpense: Float of the total expense in this day.
     *          balance: Float of the balance in this day.
     */
    List<Map<String, ?>> queryDailySummaryList(String accountGroupId, List<Date> dates) {
        // Gets unique date from the list.
        def uniqueDates = new HashSet<Date>(dates)

        // Queries the summary based on these dates and returns the result.
        return  queryDailySummaryList(accountGroupId, uniqueDates)
    }

    /**
     * Queries all distinct income/expense years.
     * @param accountGroupId: The string of the user account group id.
     * @return The list of Integer of existing year.
     */
    List<Integer> queryExistingDistinctYears(String accountGroupId) {
        def hql = """
                    SELECT DISTINCT
                        YEAR(incomeExpenseDate)
                    FROM
                        IncomeExpenseDetail
                    WHERE
                        accountGroup.id = :accountGroupId
                    ORDER BY
                        YEAR(incomeExpenseDate) DESC
                  """

        return IncomeExpenseDetail.executeQuery(hql, [accountGroupId: accountGroupId]).sort().reverse()
    }

    /**
     * Queries summary data of provided year.
     * @param accountGroupId: The string of the user account group id.
     * @param year: The int of year whose summary data will be retrieved.
     * @return The map which contains return result of querySummaryByYears method.
     */
    Map<String, ?> queryAnnualSummary(String accountGroupId, Integer year) {
        def result = queryAnnualSummaryByYears(accountGroupId, [year])

        return result?.first()
    }

    /**
     * Queries the summary based on the type and year for each month.
     * @param accountGroupId: The string of the user account group id.
     * @param type: The string of the income/expense type.
     * @param paymentType: The string of the payment type.
     * @param year: The Integer of year.
     * @return The map instance which contains keys below.
     *          (year): The double of the total amount of this year.
     *          (month): The double of the total amount of this month.
     */
    @CompileDynamic
    Map<String, ?> queryAnnualMonthlySummary(String accountGroupId, String type, String paymentType, Integer year) {
        Map<String, ?> result = [:]
        BigDecimal totalAmount = 0.0

        // Queries all existing monthly amount data in this year.
        String hql = """
                SELECT
                    MONTH(detail.incomeExpenseDate),
                    COALESCE(SUM(detail.amount), 0)
                FROM
                    IncomeExpenseDetail AS detail
                INNER JOIN
                    IncomeExpenseDetailType AS detailType ON detailType.name = detail.detailType AND detailType.accountGroup.id = detail.accountGroup.id
                WHERE
                    detailType.analysisIncluded = :analysisIncluded AND
                    detail.accountGroup.id = :accountGroupId AND
                    YEAR(detail.incomeExpenseDate) = :year AND
                    detail.type = :type AND
                    detail.paymentType = :paymentType
                GROUP BY
                    MONTH(detail.incomeExpenseDate)
              """

        def existingMonthlyAmountList = IncomeExpenseDetail.executeQuery(hql, [
            accountGroupId: accountGroupId,
            analysisIncluded: true,
            year: year,
            type: type,
            paymentType: paymentType
        ]).collect {[
            month: it[0],
            amount: it[1]
        ]}

        // Prepares the amount of 12 months based on existing monthly amount data.
        12.times { Integer index ->
            // Index is 0 based.
            def month = index + 1

            // The amount of each month.
            BigDecimal amount = 0.0

            // Gets the monthly amount data from query result for current month.
            def monthlyAmount = existingMonthlyAmountList.find { it.month == month }

            // If monthly data exists then updates amount.
            if (monthlyAmount != null) {
                amount = monthlyAmount.amount as BigDecimal
            }

            // Calculates the annual total amount.
            totalAmount += amount as BigDecimal

            // Formats the month to string. Example: 1 to 01.
            def monthString = new DecimalFormat("00").format(month)

            // Puts the amount into the map with month string as key.
            result.put(monthString, amount)
        }

        // Puts the total amount into the map with year string as key.
        result.put("${year}", totalAmount)

        return result
    }

    /**
     * Queries the summary based on the type and month for each day.
     * @param accountGroupId: The string of the user account group id.
     * @param type: The string of the income/expense type.
     * @param paymentType: The string of the payment type.
     * @param year: The Integer of year.
     * @return The map instance which contains keys below.
     *          (year): The double of the total amount of this year.
     *          (month): The double of the total amount of this month.
     */
    @CompileDynamic
    Map<String, ?> queryMonthlyDailySummary(String accountGroupId, String type, String paymentType, Integer year, Integer month) {
        Map<String, ?> result = [:]
        BigDecimal totalAmount = 0.0

        // Queries all existing daily amount data in this month.
        String hql = """
                SELECT
                    DAY(detail.incomeExpenseDate),
                    COALESCE(SUM(detail.amount), 0)
                FROM
                    IncomeExpenseDetail AS detail
                INNER JOIN
                    IncomeExpenseDetailType AS detailType ON detailType.name = detail.detailType AND detailType.accountGroup.id = detail.accountGroup.id
                WHERE
                    detailType.analysisIncluded = :analysisIncluded AND
                    detail.accountGroup.id = :accountGroupId AND
                    YEAR(detail.incomeExpenseDate) = :year AND
                    MONTH(detail.incomeExpenseDate) = :month AND
                    detail.type = :type AND
                    detail.paymentType = :paymentType
                GROUP BY
                    DAY(detail.incomeExpenseDate)
              """

        def existingDailyAmountList = IncomeExpenseDetail.executeQuery(hql, [
            accountGroupId: accountGroupId,
            analysisIncluded: true,
            year: year,
            month: month,
            type: type,
            paymentType: paymentType
        ]).collect {[
            day: it[0],
            amount: it[1]
        ]}

        int lastDay = DateHelper.getLastDayOfMonth(year, month)[Calendar.DAY_OF_MONTH] as int

        // Prepares the amount of days based on existing daily amount data.
        lastDay.times { int index ->
            // Index is 0 based.
            def day = index + 1

            // The amount of each day.
            BigDecimal amount = 0.0

            // Gets the daily amount data from query result for current day.
            def dailyAmount = existingDailyAmountList.find { it.day == day }

            // If daily data exists then updates amount.
            if (dailyAmount != null) {
                amount = dailyAmount.amount as BigDecimal
            }

            // Calculates the annual total amount.
            totalAmount += amount as BigDecimal

            // Formats the month to string. Example: 1 to 01.
            def dayString = new DecimalFormat("00").format(day)

            // Puts the amount into the map with month string as key.
            result.put(dayString, amount)
        }

        // Puts the total amount into the map with year string as key.
        result.put("${year}${month}", totalAmount)

        return result
    }

    /**
     * Queries the top bank top order by amount based on type and payment type in particular year and month.
     * @param accountGroupId: The string of the user account group id.
     * @param pageSize: How many records is return in one trip.
     * @param pageIndex: Where to take rows.
     * @param type: The string of income/expense type.
     * @param paymentType: The string of payment type.
     * @param year: The Integer of year.
     * @param month: The Integer of month. It can be null.
     * @return The list of map instance which contains keys below.
     *          id: String of the income expense detail id.
     *          incomeExpenseDate: Date of the income expense detail.
     *          amount: Float of the income expense detail amount.
     *          type: String of the type. It's income or expense.
     *          detailType: String of the detail type which indicates where the income from or the expense goes.
     *          paymentType: String of the payment type which how the expense payed.
     *          bankId: String of the band id of the bank if the expense is payed by card.
     *          bankShortName: String of the short name of the bank if the expense is payed by card.
     *          bankLogoUrl: String of the logo url of the bank if the expense is payed by card.
     *          bankCardId: String of the id of the bank card if the expense is payed by card.
     *          bankCardName: String of the name of the bank card if the expense is payed by card.
     *          bankCardIsCreditCard: Bool of if this bank card is credit card.
     *          details: String of the income or expense detail.
     */
    @CompileDynamic
    List<Map<String, ?>> queryTopRankByAccountGroup(String accountGroupId, Integer pageSize, Integer pageIndex, String type, String paymentType, Integer year, Integer month) {
        def hql = """
                    SELECT 
                        detail.id,
                        detail.incomeExpenseDate,
                        detail.amount,
                        detail.type,
                        detail.detailType,
                        detail.paymentType,
                        bank.id,
                        bank.shortName,
                        bankCard.id,
                        bankCard.name,
                        bankCard.isCreditCard,
                        detail.details
                    FROM
                        IncomeExpenseDetail AS detail
                    INNER JOIN 
                        IncomeExpenseDetailType AS detailType ON detailType.name = detail.detailType
                    LEFT OUTER JOIN
                        BankCard AS bankCard ON bankCard.id = detail.bankCard.id
                    LEFT OUTER JOIN
                        Bank AS bank ON bank.id = bankCard.bank.id
                    WHERE
                        detailType.accountGroup.id = :accountGroupId AND
                        detailType.analysisIncluded = :analysisIncluded AND
                        detail.accountGroup.id = :accountGroupId AND
                        detail.type = :type AND
                        (detail.paymentType = :paymentType OR :paymentType IS NULL OR :paymentType = '') AND
                        (YEAR(detail.incomeExpenseDate) = :year OR :year IS NULL) AND
                        (MONTH(detail.incomeExpenseDate) = :month OR :month IS NULL)
                    ORDER BY
                        ABS(detail.amount) DESC
                  """

        IncomeExpenseDetail.executeQuery(hql, [
            accountGroupId: accountGroupId,
            type: type,
            paymentType: paymentType,
            analysisIncluded: true,
            year: year,
            month: month
        ], [
            max: pageSize,
            offset: pageSize * pageIndex
        ]).collect {[
            id: it[0],
            incomeExpenseDate: it[1],
            amount: it[2],
            type: it[3],
            detailType: it[4],
            paymentType: it[5],
            bankId: it[6],
            bankShortName: it[7],
            bankLogoUrl: bankService.getLogoUrl(it[7].toString()),
            bankCardId: it[8],
            bankCardName: it[9],
            bankCardIsCreditCard: it[10],
            details: it[11]
        ]}
    }

    /**
     * Queries the expense summary based on the type and year.
     * It focuses on expense detail type and does not include the credit card repayment.
     * @param accountGroupId: The string of the user account group id.
     * @param paymentType: The string of payment type.
     * @param year: The Integer of year.
     * @param month: The Integer of month. This can be null.
     * @return The list of map instance which contains keys below.
     *          detailType: The sting of the detail type. The value is corresponding amount.
     *          amount: The corresponding amount of this detail type.
     */
    List<Map<String, ?>> queryExpenseByDetailType(String accountGroupId, String paymentType, Integer year, Integer month) {
        // Queries the detail type names.
        def detailTypes = incomeExpenseDetailTypeService.queryNameForAnalysisByAccountGroup(accountGroupId, IncomeExpenseTypeConstant.EXPENSE)

        return querySummaryByDetailType(accountGroupId, paymentType, year, month, IncomeExpenseTypeConstant.EXPENSE, detailTypes)
    }

    /**
     * Queries the income summary based on the type and year.
     * @param accountGroupId: The string of the user account group id.
     * @param paymentType: The string of payment type.
     * @param year: The Integer of year.
     * @param month: The Integer of month. This can be null.
     * @return The list of map instance which contains keys below.
     *          detailType: The sting of the detail type. The value is corresponding amount.
     *          amount: The corresponding amount of this detail type.
     */
    List<Map<String, ?>> queryIncomeByDetailType(String accountGroupId, String paymentType, Integer year, Integer month) {
        // Queries the detail type names.
        def detailTypes = incomeExpenseDetailTypeService.queryNameForAnalysisByAccountGroup(accountGroupId, IncomeExpenseTypeConstant.INCOME)

        return querySummaryByDetailType(accountGroupId, paymentType, year, month, IncomeExpenseTypeConstant.INCOME, detailTypes)
    }

    /**
     * Queries the overall summary for account group.
     * @param accountGroupId: The string of the user's account group id.
     * @return The list of map instance which contains keys below.
     *          cashIncome: The total cash income amount of overall.
     *          cashExpense: The total cash expense amount of overall.
     *          cashBalance: The total cash balance amount of overall.
     *          creditCardIncome: The total credit card income amount of overall.
     *          creditCardExpense: The total credit card expense amount of overall.
     *          creditCardBalance: The total credit card balance amount of overall.
     */
    @CompileDynamic
    Map<String, Map<String, ?>> queryOverallSummary(String accountGroupId) {
        Map<String, Map<String, ?>> result = [
            cashIncomeSummary: [:],
            cashExpenseSummary: [:],
            creditCardIncomeSummary: [:],
            creditCardExpenseSummary: [:]
        ]

        // Queries all SUM data for each year.
        // By now IncomeExpenseDetail and IncomeExpenseDetailType have no reference field. That's means no foreign key.
        // That's caused join operation takes 250+ms but sub-query takes 10+ms.
        def hql = """
                    SELECT
                        YEAR(incomeExpenseDate),
                        type,
                        paymentType,
                        COALESCE(SUM(amount), 0)
                    FROM
                        IncomeExpenseDetail
                    WHERE
                        detailType IN (
                            SELECT
                                name
                            FROM
                                IncomeExpenseDetailType
                            WHERE
                                accountGroup.id = :accountGroupId AND
                                analysisIncluded = :analysisIncluded
                        ) AND 
                        accountGroup.id = :accountGroupId
                    GROUP BY
                        YEAR(incomeExpenseDate),
                        type,
                        paymentType
                  """

        def overallSummary = IncomeExpenseDetail.executeQuery(hql, [
            accountGroupId: accountGroupId,
            analysisIncluded: true
        ]).collect {[
            year: it[0],
            type: it[1],
            paymentType: it[2],
            amount: it[3]
        ]}

        List<Integer> distinctYears = overallSummary.year.unique()
        def cashIncomeSummary = 0.0
        def totalCashIncomeSummary = 0.0
        def cashExpenseSummary = 0.0
        def totalCashExpenseSummary = 0.0
        def creditCardIncomeSummary = 0.0
        def totalCreditCardIncomeSummary = 0.0
        def creditCardExpenseSummary = 0.0
        def totalCreditCardExpenseSummary = 0.0

        distinctYears.each { Integer year ->
            cashIncomeSummary = overallSummary.findAll { Map<String, ?> summary ->
                summary.year == year &&
                    summary.type == IncomeExpenseTypeConstant.INCOME &&
                    summary.paymentType == PaymentTypeConstant.CASH
            }?.amount.sum() ?: 0.0

            totalCashIncomeSummary += cashIncomeSummary
            result.cashIncomeSummary.put(year.toString(), cashIncomeSummary)

            cashExpenseSummary = overallSummary.findAll { Map<String, ?> summary ->
                summary.year == year &&
                    summary.type == IncomeExpenseTypeConstant.EXPENSE &&
                    summary.paymentType == PaymentTypeConstant.CASH
            }?.amount.sum() ?: 0.0

            totalCashExpenseSummary += cashExpenseSummary
            result.cashExpenseSummary.put(year.toString(), cashExpenseSummary)

            creditCardIncomeSummary = overallSummary.findAll { Map<String, ?> summary ->
                summary.year == year &&
                    summary.type == IncomeExpenseTypeConstant.INCOME &&
                    summary.paymentType == PaymentTypeConstant.CREDIT_CARD
            }?.amount.sum() ?: 0.0

            totalCreditCardIncomeSummary += creditCardIncomeSummary
            result.creditCardIncomeSummary.put(year.toString(), creditCardIncomeSummary)

            creditCardExpenseSummary = overallSummary.findAll { Map<String, ?> summary ->
                summary.year == year &&
                    summary.type == IncomeExpenseTypeConstant.EXPENSE &&
                    summary.paymentType == PaymentTypeConstant.CREDIT_CARD
            }?.amount.sum() ?: 0.0

            totalCreditCardExpenseSummary += creditCardExpenseSummary
            result.creditCardExpenseSummary.put(year.toString(), creditCardExpenseSummary)
        }

        result.cashIncomeSummary.put("total", totalCashIncomeSummary)
        result.cashExpenseSummary.put("total", totalCashExpenseSummary)
        result.creditCardIncomeSummary.put("total", totalCreditCardIncomeSummary)
        result.creditCardExpenseSummary.put("total", totalCreditCardExpenseSummary)

        return result
    }

    /**
     * Queries the annual summary in the specific years.
     * @param accountGroupId: The string of the user's account group id.
     * @param years: The Integer of the year.
     * @return The list of map instance which contains keys below.
     *          year: The year of these summary.
     *          cashIncome: The total cash income amount of this year.
     *          cashExpense: The total cash expense amount of this year.
     *          cashBalance: The total cash balance amount of this year.
     *          creditCardIncome: The total credit card income amount of this year.
     *          creditCardExpense: The total credit card expense amount of this year.
     *          creditCardBalance: The total credit card balance amount of this year.
     */
    @CompileDynamic
    List<Map<String, ?>> queryAnnualSummaryByYears(String accountGroupId, List<Integer> years) {
        List<Map<String, ?>> result = []

        if (!years?.empty) {
            // Queries all SUM data for each year.
            // By now IncomeExpenseDetail and IncomeExpenseDetailType have no reference field. That's means no foreign key.
            // That's caused join operation takes 250+ms but sub-query takes 10+ms.
            def hql = """
                    SELECT
                        YEAR(incomeExpenseDate),
                        type,
                        paymentType,
                        COALESCE(SUM(amount), 0)
                    FROM
                        IncomeExpenseDetail
                    WHERE
                        detailType IN (
                            SELECT
                                name
                            FROM
                                IncomeExpenseDetailType
                            WHERE
                                accountGroup.id = :accountGroupId AND
                                analysisIncluded = :analysisIncluded
                        ) AND 
                        accountGroup.id = :accountGroupId AND
                        YEAR(incomeExpenseDate) IN :years
                    GROUP BY
                        YEAR(incomeExpenseDate),
                        type,
                        paymentType
                  """

            def summaryList = IncomeExpenseDetail.executeQuery(hql, [
                accountGroupId: accountGroupId,
                analysisIncluded: true,
                years: years
            ]).collect {[
                year: it[0],
                type: it[1],
                paymentType: it[2],
                amount: it[3]
            ]}

            def totalCashIncome = 0.0
            def totalCashExpense = 0.0
            def totalCreditCardIncome = 0.0
            def totalCreditCardExpense = 0.0

            years.each { Integer year ->
                def cashIncome = summaryList.find { Map<String, ?> summary ->
                    summary.year == year &&
                        summary.type == IncomeExpenseTypeConstant.INCOME &&
                        summary.paymentType == PaymentTypeConstant.CASH
                }?.amount ?: 0.0

                def cashExpense = summaryList.find { Map<String, ?> summary ->
                    summary.year == year &&
                        summary.type == IncomeExpenseTypeConstant.EXPENSE &&
                        summary.paymentType == PaymentTypeConstant.CASH
                }?.amount ?: 0.0

                def creditCardIncome = summaryList.find { Map<String, ?> summary ->
                    summary.year == year &&
                        summary.type == IncomeExpenseTypeConstant.INCOME &&
                        summary.paymentType == PaymentTypeConstant.CREDIT_CARD
                }?.amount ?: 0.0

                def creditCardExpense = summaryList.find { Map<String, ?> summary ->
                    summary.year == year &&
                        summary.type == IncomeExpenseTypeConstant.EXPENSE &&
                        summary.paymentType == PaymentTypeConstant.CREDIT_CARD
                }?.amount ?: 0.0

                totalCashIncome += cashIncome
                totalCashExpense += cashExpense
                totalCreditCardIncome += creditCardIncome
                totalCreditCardExpense += creditCardExpense

                result << [
                    year: year,
                    cashIncome: cashIncome,
                    cashExpense: cashExpense,
                    cashBalance: cashIncome + cashExpense,
                    creditCardIncome: creditCardIncome,
                    creditCardExpense: creditCardExpense,
                    creditCardBalance: creditCardIncome + creditCardExpense
                ]
            }

            result.add(0, [
                year: "${years.last()} - ${years.first()}",
                cashIncome: totalCashIncome,
                cashExpense: totalCashExpense,
                cashBalance: totalCashIncome + totalCashExpense,
                creditCardIncome: totalCreditCardIncome,
                creditCardExpense: totalCreditCardExpense,
                creditCardBalance: totalCreditCardIncome + totalCreditCardExpense
            ])
        }

        return result
    }

    /**
     * Queries the summary based on the year and month, type and detail types.
     * @param accountGroupId: The string of the user's account group id.
     * @param paymentType: The string of payment type.
     * @param year: The Integer of year.
     * @param month: The Integer of month.
     * @param type: The string of IncomeExpenseTypeConstant.
     * @detailTypes: The list string of ExpenseTypeConstant or IncomeTypeConstant.
     * @return The list of map instance which contains keys below.
     *          detailType: The sting of the detail type. The value is corresponding amount.
     *          amount: The corresponding amount of this detail type.
     */
    @CompileDynamic
    private List<Map<String, ?>> querySummaryByDetailType(String accountGroupId, String paymentType, Integer year, Integer month, String type, List<String> detailTypes) {
        List<Map<String, ?>> result = []

        if (!detailTypes?.empty) {
            def hql = """
                        SELECT
                            detailType,
                            COALESCE(SUM(amount), 0)
                        FROM
                            IncomeExpenseDetail
                        WHERE
                            accountGroup.id = :accountGroupId AND
                            (paymentType = :paymentType OR :paymentType IS NULL OR :paymentType = '') AND
                            (YEAR(incomeExpenseDate) = :year OR :year IS NULL) AND
                            (MONTH(incomeExpenseDate) = :month OR :month IS NULL) AND
                            type = :type AND
                            detailType IN :detailTypes
                        GROUP BY
                            detailType
                        ORDER BY
                            ABS(SUM(amount)) DESC
                      """

            result = IncomeExpenseDetail.executeQuery(hql, [
                accountGroupId: accountGroupId,
                paymentType: paymentType,
                year: year,
                month: month,
                type: type,
                detailTypes: detailTypes
            ]).collect {[
                detailType: it[0],
                amount: it[1]
            ]} as List<Map<String, ?>>
        }

        return result
    }
}